﻿/*
Top 10 Favorite Performance Counters in Web Site Load Testing Using VSTS 2008

Microsoft Visual Studio Team System/Test Edition provides an excellent tool to perform web site load testing. Using this load testing tool, you can monitor and measure the site performance along with system status with respect to a given load/stress. 
Fortunately VSTS provides a support for wide range of performance counters, from web page request per second to condition of physical disk, memories. Unfortunately, they are too huge that, initially testers/designers get overwhelmed with all of those, to find out a clear idea about the performance of the site they built. 
The number of counter parameters to be considered by the load tester/designers is greatly varies based on the type and size of the web application to be tested. Here is my favorite top 10 performance counters that I use on my each load tests, regardless of project size. These counters are based two primary categories: Web Site end and Hardware end.
*/
   
-- Network Mix
SELECT     NetworkName
FROM         LoadTestNetworks
WHERE LoadTestRunId = 
   ( 
      select max(loadtestrunid) 
      from loadtestrun
   )

-- Browser Mix
SELECT     BrowserName
FROM       LoadTestBrowsers
WHERE LoadTestBrowsers.LoadTestRunId = 
   ( 
      select max(loadtestrunid) 
      from loadtestrun
   )

-- Performance Counters

SELECT		counter.CounterName, 
			instance.CumulativeValue as Value
FROM         dbo.LoadTestPerformanceCounterCategory AS category INNER JOIN
dbo.LoadTestPerformanceCounter AS counter ON category.LoadTestRunId = counter.LoadTestRunId AND 
category.CounterCategoryId = counter.CounterCategoryId INNER JOIN
dbo.LoadTestPerformanceCounterInstance AS instance ON counter.CounterId = instance.CounterId AND 
counter.LoadTestRunId = instance.LoadTestRunId
WHERE 
(
	-- Web Site Related Performance Counters
	(counter.CounterName = 'Requests/Sec' and instance.Instancename = '_Total') OR
	(counter.CounterName = 'Passed Requests/Sec' and instance.Instancename = '_Total') OR
	(counter.CounterName = 'Avg. Page Time'  and instance.Instancename = '_Total') OR
	(counter.CounterName = 'Total Tests' and instance.Instancename = '_Total') OR
	(counter.CounterName = 'User Load' and instance.Instancename = '_Total') OR
	(counter.CounterName = 'Http Errors' and instance.Instancename = '_Total') OR

	-- Hardware Related Performance Counters
	(counter.CounterName = '% Processor Time' and instance.Instancename = '_Total') OR
	counter.CounterName = 'Available MBytes' OR
	counter.CounterName = 'Output Queue Length' OR
	(counter.CounterName = 'Current Disk Queue Length' and instance.Instancename = '_Total')
)

AND
-- and the latest test
category.LoadTestRunId = 
   ( 
	select max(loadtestrunid) from loadtestrun
   )

